require('dotenv').config({ path: './nestjs-app/.env' });
const mysql = require('mysql2/promise');

async function verifyLevelExperienceData() {
    console.log('🔍 开始验证角色升级经验数据...');

    const connection = await mysql.createConnection({
        host: process.env.DB_HOST,
        user: process.env.DB_USER,
        password: process.env.DB_PASS,
        database: process.env.DB_NAME,
        port: parseInt(process.env.DB_PORT || '3306', 10)
    });

    try {
        await connection.connect();
        console.log('✅ 数据库连接成功！');

        // 1. 检查总记录数
        const [countRows] = await connection.execute('SELECT COUNT(*) AS count FROM level_experience');
        console.log(`📊 总记录数: ${countRows[0].count}`);

        // 2. 检查等级范围
        const [rangeRows] = await connection.execute('SELECT MIN(level) as min_level, MAX(level) as max_level FROM level_experience');
        console.log(`📈 等级范围: ${rangeRows[0].min_level} - ${rangeRows[0].max_level}`);

        // 3. 检查经验值范围
        const [expRangeRows] = await connection.execute('SELECT MIN(experience) as min_exp, MAX(experience) as max_exp FROM level_experience');
        console.log(`💎 经验值范围: ${expRangeRows[0].min_exp.toLocaleString()} - ${expRangeRows[0].max_exp.toLocaleString()}`);

        // 4. 验证关键等级数据
        console.log('\n📋 关键等级数据验证:');
        const keyLevels = [1, 10, 20, 30, 50, 80, 100, 120, 150, 159];
        for (const level of keyLevels) {
            const [result] = await connection.execute(
                'SELECT level, experience FROM level_experience WHERE level = ?',
                [level]
            );
            if (result.length > 0) {
                console.log(`  等级 ${level}: ${result[0].experience.toLocaleString()} 经验`);
            } else {
                console.log(`  ❌ 等级 ${level}: 数据缺失`);
            }
        }

        // 5. 验证经验值递增（使用兼容的SQL）
        console.log('\n📈 经验值递增验证:');
        const [increasingCheck] = await connection.execute(`
            SELECT 
                l1.level,
                l1.experience,
                l2.experience as prev_experience,
                CASE WHEN l1.experience > l2.experience THEN '✅' ELSE '❌' END as is_increasing
            FROM level_experience l1
            LEFT JOIN level_experience l2 ON l1.level = l2.level + 1
            WHERE l1.level > 1
            ORDER BY l1.level
        `);

        let increasingCount = 0;
        let totalCount = increasingCheck.length;
        
        // 检查前10个和后10个等级
        console.log('  前10个等级递增检查:');
        for (let i = 0; i < Math.min(10, totalCount); i++) {
            const row = increasingCheck[i];
            console.log(`    等级 ${row.level}: ${row.is_increasing} (${row.prev_experience.toLocaleString()} → ${row.experience.toLocaleString()})`);
            if (row.is_increasing === '✅') increasingCount++;
        }

        console.log('  后10个等级递增检查:');
        for (let i = Math.max(0, totalCount - 10); i < totalCount; i++) {
            const row = increasingCheck[i];
            console.log(`    等级 ${row.level}: ${row.is_increasing} (${row.prev_experience.toLocaleString()} → ${row.experience.toLocaleString()})`);
            if (row.is_increasing === '✅') increasingCount++;
        }

        console.log(`\n📊 递增验证结果: ${increasingCount}/${totalCount} 个等级符合递增规律`);

        // 6. 检查是否有重复等级
        const [duplicateCheck] = await connection.execute(`
            SELECT level, COUNT(*) as count 
            FROM level_experience 
            GROUP BY level 
            HAVING COUNT(*) > 1
        `);
        
        if (duplicateCheck.length === 0) {
            console.log('✅ 无重复等级数据');
        } else {
            console.log('❌ 发现重复等级数据:');
            duplicateCheck.forEach(row => {
                console.log(`  等级 ${row.level}: ${row.count} 条记录`);
            });
        }

        // 7. 检查是否有缺失等级
        const [missingCheck] = await connection.execute(`
            SELECT t1.level + 1 as missing_level
            FROM level_experience t1
            LEFT JOIN level_experience t2 ON t1.level + 1 = t2.level
            WHERE t2.level IS NULL AND t1.level < (SELECT MAX(level) FROM level_experience)
        `);
        
        if (missingCheck.length === 0) {
            console.log('✅ 无缺失等级数据');
        } else {
            console.log('❌ 发现缺失等级:');
            missingCheck.forEach(row => {
                console.log(`  等级 ${row.missing_level}`);
            });
        }

        // 8. 统计信息
        console.log('\n📊 数据统计:');
        const [stats] = await connection.execute(`
            SELECT 
                AVG(experience) as avg_experience,
                STDDEV(experience) as stddev_experience
            FROM level_experience
        `);
        console.log(`  平均经验值: ${Math.round(stats[0].avg_experience).toLocaleString()}`);
        console.log(`  经验值标准差: ${Math.round(stats[0].stddev_experience).toLocaleString()}`);

        console.log('\n🎉 角色升级经验数据验证完成！');

    } catch (error) {
        console.error('❌ 验证过程中发生错误:', error.message);
    } finally {
        await connection.end();
        console.log('🔌 数据库连接已关闭');
    }
}

verifyLevelExperienceData();
